﻿using CBS.DB;
using CBS.DB.CBSEntity;
using CBS.Model.ManageModel;
using CBS.Model.UIModel;
using CBS.Model.ViewModel;
using MathNet.Numerics.Distributions;
using MySqlConnector;
using NPOI.SS.Formula.Functions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CBS.APP
{
    public class SystemStatementApp
    {
        public static List<StudentSignDetailStatementViewModel> GetStudentSignDetailStatementList(Pagination page, int semesterId, int gradeId, int classId, int courseId, int teacherId, string studentName, string coursePlan)
        {
            string sql = @"select a.*,b.StartTime,f.SemesterName,e.TeacherName,d.StudentName,g.GradeName,h.ClassName,i.CourseName,c.IsTaster
from cbs_course_plan a
	inner join cbs_lesson b on a.CoursePlanId = b.CoursePlanId
	inner join cbs_student_sign_detail c on b.LessonId = c.LessonId
	left join cbs_student d on c.StudentId = d.StudentId
	left join cbs_teacher e on b.TeacherId = e.TeacherId
	left join cbs_semester f on a.SemesterId = f.SemesterId
	left join cbs_grade g on a.GradeId = g.GradeId
	left join cbs_class h on a.ClassId = h.ClassId
	left join cbs_course i on a.CourseId = i.CourseId
where (a.SemesterId = @semesterId)
and (@gradeId = 0 or a.GradeId = @gradeId)
and (@classId = 0 or a.ClassId = @classId)
and (@courseId = 0 or a.CourseId = @courseId)
and (@teacherId = 0 or b.TeacherId = @teacherId)
and (@coursePlan = '' or a.CoursePlanName like @coursePlan)
and (@studentName = '' or d.StudentName like @studentName)";
            MySqlParameter[] para =
            {
                new MySqlParameter("@semesterId",semesterId),
                new MySqlParameter("@gradeId",gradeId),
                new MySqlParameter("@classId",classId),
                new MySqlParameter("@courseId",courseId),
                new MySqlParameter("@coursePlan",$"%{coursePlan}%"),
                new MySqlParameter("@teacherId",teacherId),
                new MySqlParameter("@studentName",$"%{studentName}%"),
            };
            return DBHelper.FindListOrderBy<StudentSignDetailStatementViewModel>(sql, page, FromPositionInSql.First, para);
        }

        public static DataTable GetStudentSignDetailStatementDataTable(int semesterId, int classPlanId, string studentName)
        {
            string sql = @"select a.*,b.StartTime,f.SemesterName,e.TeacherName,d.StudentName,g.GradeName,h.ClassName,i.CourseName
from cbs_course_plan a
	inner join cbs_lesson b on a.CoursePlanId = b.CoursePlanId
	inner join cbs_student_sign_detail c on b.LessonId = c.LessonId
	left join cbs_student d on c.StudentId = d.StudentId
	left join cbs_teacher e on b.TeacherId = e.TeacherId
	left join cbs_semester f on a.SemesterId = f.SemesterId
	left join cbs_grade g on a.GradeId = g.GradeId
	left join cbs_class h on a.ClassId = h.ClassId
	left join cbs_course i on a.CourseId = i.CourseId
where (a.SemesterId = @semesterId)
and (@studentName = '' or d.StudentName like @studentName)";
            MySqlParameter[] para =
            {
                new MySqlParameter("@semesterId",semesterId),
                //new MySqlParameter("@classPlanId",classPlanId),
                new MySqlParameter("@studentName",$"%{studentName}%"),
            };
            return DBHelper.FindList(sql, para);
        }

        public static bool GetStudentSignDetailStatementExcel(string fileName, int semesterId, int gradeId, int classId, int courseId, int teacherId, string studentName, string coursePlan)
        {
            CBSContext cbs = new();
            var data = (from courseplan in cbs.cbs_course_plan
                        join lesson in cbs.cbs_lesson on courseplan.CoursePlanId equals lesson.CoursePlanId
                        join signin in cbs.cbs_student_sign_detail on lesson.LessonId equals signin.LessonId
                        join stu in cbs.cbs_student on signin.StudentId equals stu.StudentId
                        where courseplan.SemesterId == semesterId
                        && (teacherId == 0 || lesson.TeacherId == teacherId)
                        && (gradeId == 0 || courseplan.GradeId == gradeId)
                        && (classId == 0 || courseplan.ClassId == classId)
                        && (courseId == 0 || courseplan.CourseId == courseId)
                        && (studentName == "" || stu.StudentName.Contains(studentName))
                        && (coursePlan == "" || courseplan.CoursePlanName.Contains(coursePlan))
                        select new { stu.StudentId, stu.StudentName }).ToList();

            string sql = @$"select a.CoursePlanName,c.StudentId,d.GradeName,e.ClassName,f.CourseName,b.StartTime,b.EndTime,b.TeacherId,g.TeacherName,h.StudentName,c.IsTaster,c.IsSupplement
from cbs_course_plan a
inner join cbs_lesson b on a.CoursePlanId = b.CoursePlanId
inner join cbs_student_sign_detail c on b.LessonId = c.LessonId
left join cbs_grade d on a.GradeId = d.GradeId
left join cbs_class e on a.ClassId = e.ClassId
left join cbs_course f on a.CourseId = f.CourseId
left join cbs_teacher g on b.TeacherId = g.TeacherId
left join cbs_student h on c.StudentId = h.StudentId
where a.SemesterId = {semesterId} and c.StudentId = {{0}}
order by a.CoursePlanName,d.GradeName,e.ClassName,f.CourseName,b.StartTime;
";

            if (data.Count == 0)
            {
                return false;
            }

            int[] stuids = data.Select(t => t.StudentId).ToArray().Distinct().ToArray();
            List<string> tmpStudentName = new List<string>();
            foreach (var stu in stuids)
            {
                tmpStudentName.Add(data.First(t => t.StudentId == stu).StudentName);
            }

            string fullsql = "";
            foreach (var item in stuids)
            {
                fullsql = string.Concat(fullsql, string.Format(sql, item));
            }

            var ds = DBHelper.FindDataSet(fullsql, tmpStudentName);

            var signUpLessonData = (from courseplan in cbs.cbs_course_plan
                                    join signUp in cbs.cbs_student_signup on courseplan.CoursePlanId equals signUp.CoursePlanId
                                    join course in cbs.cbs_course on courseplan.CourseId equals course.CourseId
                                    join lesson in cbs.cbs_lesson on courseplan.CoursePlanId equals lesson.CoursePlanId
                                    join stu in cbs.cbs_student on signUp.StudentId equals stu.StudentId
                                    join signIn in cbs.cbs_student_sign_detail on new { lesson.LessonId, stu.StudentId } equals new { signIn.LessonId, signIn.StudentId }
                                    join tea in cbs.cbs_teacher on lesson.TeacherId equals tea.TeacherId
                                    where courseplan.SemesterId == semesterId
                                    && (teacherId == 0 || lesson.TeacherId == teacherId)
                                    && (gradeId == 0 || courseplan.GradeId == gradeId)
                                    && (classId == 0 || courseplan.ClassId == classId)
                                    && (courseId == 0 || courseplan.CourseId == courseId)
                                    && (coursePlan == "" || courseplan.CoursePlanName.Contains(coursePlan))
                                    && (studentName == "" || stu.StudentName.Contains(studentName))
                                    select new { stu.StudentId, stu.StudentName, courseplan.CourseId, course.CourseName, lesson.StartTime, lesson.EndTime, signIn.IsTaster, signIn.IsSupplement }).ToList();

            //int[] stuids = data.Select(t => t.StudentId).ToArray();
            var stuSemesterAmount = cbs.cbs_student_semester_amount.Where(t => t.SemesterId == semesterId && stuids.Contains(t.StudentId)).ToList();
            var stuPayments = (from pay in cbs.cbs_student_payment
                               join tea in cbs.cbs_teacher on pay.TeacherId equals tea.TeacherId
                               where pay.PaymentSemester == semesterId && stuids.Contains(pay.StudentId)
                               select new StudentPaymentDetail
                               {
                                   StudentId = pay.StudentId,
                                   TeacherId = pay.TeacherId,
                                   TeacherName = tea.TeacherName,
                                   Payment = pay.Payment,
                                   BeforeAmount = pay.BeforeAmount,
                                   PayTime = pay.PaymentTime.Value,
                               }).ToList();

            List<StudentSignStatementViewModel> detail = new List<StudentSignStatementViewModel>();
            foreach (DataTable item in ds.Tables)
            {
                List<StudentSignStatementQueryModel> tmpStu = DBHelper.DateTableToList<StudentSignStatementQueryModel>(item);

                StudentSignStatementViewModel model = new StudentSignStatementViewModel();

                var orderCourse = tmpStu.OrderBy(t => t.StartTime).ToList();
                model.StudentName = orderCourse[0].StudentName;
                model.GradeName = orderCourse[0].GradeName;
                model.StartTime = orderCourse[0].StartTime;
                model.SemesterName = orderCourse[0].SemesterName;
                model.CoursePlanName = orderCourse[0].CoursePlanName;
                model.EndTime = orderCourse[orderCourse.Count - 1].EndTime;

                var stuAmount = stuSemesterAmount.FirstOrDefault(t => t.StudentId == orderCourse[0].StudentId);
                if (stuAmount == null)
                {
                    continue;
                }

                model.Payable = stuAmount.Payable;
                model.Payment = stuAmount.Payment;
                model.BeforeAmount = stuAmount.BeforeSemesterBalance;

                var tmpStuPayments = stuPayments.Where(t => t.StudentId == orderCourse[0].StudentId).ToList().OrderBy(o => o.PayTime).ToList();
                if (tmpStuPayments.Count > 0)
                {
                    model.StudentPaymentDetail = tmpStuPayments;
                }

                var courses = tmpStu.GroupBy(t => t.CourseName).Select(g => g.Key).ToList();
                model.CourseSignDetail = new List<CourseSignDetail>();
                foreach (var course in courses)
                {
                    var courseSignDetail = new CourseSignDetail()
                    {
                        CourseName = course
                    };
                    var tmpSignIn = tmpStu.Where(t => t.CourseName == courseSignDetail.CourseName).OrderBy(o => o.StartTime).ToList();
                    courseSignDetail.SignDetail = tmpSignIn.Select(t => new SignDetail { SignDetailStartTime = t.StartTime, SignDetailEndTime = t.EndTime, ClassName = t.ClassName, IsSupplement = t.IsSupplement, IsTaster = t.IsTaster }).ToList();

                    model.CourseSignDetail.Add(courseSignDetail);
                }

                var signup = signUpLessonData.Where(t => t.StudentId == orderCourse[0].StudentId).GroupBy(g => new { g.CourseId, g.CourseName });
                foreach (var tmp in signup)
                {
                    var courseInfo = model.CourseSignDetail.FirstOrDefault(t => t.CourseName == tmp.Key.CourseName);
                    if (courseInfo != null)
                    {
                        courseInfo.LessonDetail = signUpLessonData.Where(t => t.StudentId == orderCourse[0].StudentId && t.CourseId == tmp.Key.CourseId).OrderBy(o => o.StartTime).Select(t => new LessonDetail { StartTime = t.StartTime, EndTime = t.EndTime }).ToList();
                    }
                    else
                    {
                        model.CourseSignDetail.Add(new CourseSignDetail
                        {
                            CourseName = tmp.Key.CourseName,
                            LessonDetail = signUpLessonData.Where(t => t.StudentId == orderCourse[0].StudentId && t.CourseId == tmp.Key.CourseId).OrderBy(o => o.StartTime).Select(t => new LessonDetail { StartTime = t.StartTime, EndTime = t.EndTime }).ToList()
                        });
                    }
                }


                detail.Add(model);
            }
            StatementExportApp.StudentSignInAndPaymentInfoExport(fileName, detail);
            return true;
        }

        public static List<CourseBillingStatementViewModel> GetCourseBillingStatementList(Pagination page, int semesterId, string studentName, List<CourseViewModel> courseList)
        {
            string sql = $"select b.SemesterId,b.SemesterName,c.StudentId,c.StudentName,\r\n(select top 1 BeforeAmount from cbs_student_payment where StudentId = a.StudentId and PaymentSemester < a.SemesterId order by PaymentTime desc) 上期余额,\r\n(select SUM(Payment) from cbs_student_payment where StudentId = a.StudentId and PaymentSemester = a.SemesterId) 缴费总金额,\r\n";
            foreach (var model in courseList)
            {
                sql += $"(select count(1) from cbs_student_signup where StudentId = a.StudentId and CourseId = {model.CourseId}) *b.CoursePrice as {model.CourseName},\r\n";

            }
            sql += $"from cbs_course_plan a \r\n\tleft join cbs_semester b on a.SemesterId = b.SemesterId\r\n\tleft join cbs_student c on a.StudentId = c.StudentId\r\nwhere a.SemesterId = @semesterId and (@studentName = '' or c.StudentName like @studentName)\r\ngroup by b.SemesterId,b.SemesterName,c.StudentId,c.StudentName";
            MySqlParameter[] para =
            {
                new MySqlParameter("@semesterId",semesterId),
                new MySqlParameter("@studentName",$"%{studentName}%"),
            };
            return DBHelper.FindListOrderBy<CourseBillingStatementViewModel>(sql, page, FromPositionInSql.Last, para);


        }

        public static DataTable GetCourseBillingStatementDataTable(int semesterId, string studentName, List<CourseViewModel> courseList)
        {
            string sql = $"select b.SemesterId,b.SemesterName,c.StudentId,c.StudentName,\r\n(select top 1 BeforeAmount from cbs_student_payment where StudentId = a.StudentId and PaymentSemester < a.SemesterId order by PaymentTime desc) 上期余额,\r\n(select SUM(Payment) from cbs_student_payment where StudentId = a.StudentId and PaymentSemester = a.SemesterId) 缴费总金额,\r\n";
            foreach (var model in courseList)
            {
                sql += $"(select count(1) from cbs_student_signup where StudentId = a.StudentId and CourseId = {model.CourseId}) *b.CoursePrice as {model.CourseName},\r\n";

            }
            sql += $"from cbs_course_plan a \r\n\tleft join cbs_semester b on a.SemesterId = b.SemesterId\r\n\tleft join cbs_student c on a.StudentId = c.StudentId\r\nwhere a.SemesterId = @semesterId and (@studentName = '' or c.StudentName like @studentName)\r\ngroup by b.SemesterId,b.SemesterName,c.StudentId,c.StudentName";
            MySqlParameter[] para =
            {
                new MySqlParameter("@semesterId",semesterId),
                new MySqlParameter("@studentName",$"%{studentName}%"),
            };
            return DBHelper.FindList(sql, para);
        }

        public static List<CourseBillingStatementViewModel> GetStudentBonusStatementList(Pagination page, int semesterId, string studentName)
        {
            string sql = "select a.SemesterId,a.SemesterName,c.StudentId,c.StudentName,d.StudentId IntroduceStudentId,d.StudentName IntroduceStudentName,b.BonusAmount,b.BonusTime,b.Memo\r\nfrom cbs_semester a\r\n\tleft join cbs_student_bonus b on a.SemesterId = b.SemesterId\r\n\tleft join cbs_student c on b.StudentId = c.StudentId\r\n\tleft join cbs_student d on b.IntroduceStudentId = d.StudentId\r\nwhere a.SemesterId = @semesterId and b.IsSettled = 1\r\nand (@studentName = '' or c.StudentName like @studentName)";
            MySqlParameter[] para =
            {
                new MySqlParameter("@semesterId",semesterId),
                new MySqlParameter("@studentName",$"%{studentName}%"),
            };
            return DBHelper.FindListOrderBy<CourseBillingStatementViewModel>(sql, page, FromPositionInSql.First, para);
        }

        public static DataTable GetStudentBonusStatementDataTable(int semesterId, string studentName)
        {
            string sql = "select a.SemesterName,c.StudentName,d.StudentName IntroduceStudentName,b.BonusAmount,b.BonusTime,b.Memo\r\nfrom cbs_semester a\r\n\tleft join cbs_student_bonus b on a.SemesterId = b.SemesterId\r\n\tleft join cbs_student c on b.StudentId = c.StudentId\r\n\tleft join cbs_student d on b.IntroduceStudentId = d.StudentId\r\nwhere a.SemesterId = @semesterId and b.IsSettled = 1\r\nand (@studentName = '' or c.StudentName like @studentName)";
            MySqlParameter[] para =
            {
                new MySqlParameter("@semesterId",semesterId),
                new MySqlParameter("@studentName",$"%{studentName}%"),
            };
            return DBHelper.FindList(sql, para);
        }

        public static List<CourseBillingStatementViewModel> GetStudentBonusTraceStatementList(Pagination page, int semesterId, string studentName)
        {
            string sql = "select a.SemesterId,a.SemesterName,b.ClassPlanId,b.ClassPlanName,d.TeacherId,d.TeacherName,f.StudentId,f.StudentName,c.StartTime\r\nfrom cbs_semester a\r\n    left join cbs_class b on a.SemesterId = b.SemesterId\r\n    left join cbs_lesson c on b.ClassPlanId = b.ClassPlanId\r\n    left join cbs_teacher d on c.TeacherId = d.TeacherId\r\n    left join cbs_Student_sign_detail e on c.LessonId = e.LessonId\r\n    left join cbs_student f on e.StudentId = e.StudentId\r\nwhere (a.SemesterId = @semesterId)\r\nand (@classPlanId = 0 or b.ClassPlanId = @classPlanId)\r\nand (@studentName = '' or f.StudentName like @studentName)";
            MySqlParameter[] para =
            {
                new MySqlParameter("@semesterId",semesterId),
                new MySqlParameter("@studentName",$"%{studentName}%"),
            };
            return DBHelper.FindListOrderBy<CourseBillingStatementViewModel>(sql, page, FromPositionInSql.First, para);
        }

        public static DataTable GetStudentBonusTraceStatementDataTable(int semesterId, string studentName)
        {
            string sql = "select a.SemesterId,a.SemesterName,b.ClassPlanId,b.ClassPlanName,d.TeacherId,d.TeacherName,f.StudentId,f.StudentName,c.StartTime\r\nfrom cbs_semester a\r\n    left join cbs_class b on a.SemesterId = b.SemesterId\r\n    left join cbs_lesson c on b.ClassPlanId = b.ClassPlanId\r\n    left join cbs_teacher d on c.TeacherId = d.TeacherId\r\n    left join cbs_Student_sign_detail e on c.LessonId = e.LessonId\r\n    left join cbs_student f on e.StudentId = e.StudentId\r\nwhere (a.SemesterId = @semesterId)\r\nand (@classPlanId = 0 or b.ClassPlanId = @classPlanId)\r\nand (@studentName = '' or f.StudentName like @studentName)";
            MySqlParameter[] para =
            {
                new MySqlParameter("@semesterId",semesterId),
                new MySqlParameter("@studentName",$"%{studentName}%"),
            };
            return DBHelper.FindList(sql, para);
        }

        public static List<CourseBillingStatementViewModel> GetTeacherPaybackStatementList(Pagination page, int semesterId, int classPlanId, string studentName)
        {
            string sql = "select a.SemesterId,a.SemesterName,b.ClassPlanId,b.ClassPlanName,d.TeacherId,d.TeacherName,f.StudentId,f.StudentName,c.StartTime\r\nfrom cbs_semester a\r\n    left join cbs_class b on a.SemesterId = b.SemesterId\r\n    left join cbs_lesson c on b.ClassPlanId = b.ClassPlanId\r\n    left join cbs_teacher d on c.TeacherId = d.TeacherId\r\n    left join cbs_Student_sign_detail e on c.LessonId = e.LessonId\r\n    left join cbs_student f on e.StudentId = e.StudentId\r\nwhere (a.SemesterId = @semesterId)\r\nand (@classPlanId = 0 or b.ClassPlanId = @classPlanId)\r\nand (@studentName = '' or f.StudentName like @studentName)";
            MySqlParameter[] para =
            {
                new MySqlParameter("@semesterId",semesterId),
                new MySqlParameter("@classPlanId",classPlanId),
                new MySqlParameter("@studentName",$"%{studentName}%"),
            };
            return DBHelper.FindListOrderBy<CourseBillingStatementViewModel>(sql, page, FromPositionInSql.First, para);
        }

        public static DataTable GetTeacherPaybackStatementDataTable(int semesterId, int classPlanId, string studentName)
        {
            string sql = "select a.SemesterId,a.SemesterName,b.ClassPlanId,b.ClassPlanName,d.TeacherId,d.TeacherName,f.StudentId,f.StudentName,c.StartTime\r\nfrom cbs_semester a\r\n    left join cbs_class b on a.SemesterId = b.SemesterId\r\n    left join cbs_lesson c on b.ClassPlanId = b.ClassPlanId\r\n    left join cbs_teacher d on c.TeacherId = d.TeacherId\r\n    left join cbs_Student_sign_detail e on c.LessonId = e.LessonId\r\n    left join cbs_student f on e.StudentId = e.StudentId\r\nwhere (a.SemesterId = @semesterId)\r\nand (@classPlanId = 0 or b.ClassPlanId = @classPlanId)\r\nand (@studentName = '' or f.StudentName like @studentName)";
            MySqlParameter[] para =
            {
                new MySqlParameter("@semesterId",semesterId),
                new MySqlParameter("@classPlanId",classPlanId),
                new MySqlParameter("@studentName",$"%{studentName}%"),
            };
            return DBHelper.FindList(sql, para);
        }
    }
}
